# apps/page/views.py
from datetime import date, datetime, timedelta
import time
from django.views import View
from django.shortcuts import render
from django.http import HttpResponse, JsonResponse
from django.db.models import Count, Max, Min
from django.forms.models import model_to_dict
import pandas as pd
import numpy as np
from invoice.models import Sale, GoodInSale, Purchase, GoodInPurchase, Expense
from organization.models import Store
from good.models import Good
from label.models import LabelSeries, Label, LabelStickGood
from user.models import Clerk



# Create your views here.


class SaleAlongTimeView(View):
    # 可放缩的图表
    def get(self, request, target, period, start, end, *args, **kwargs):
        # QueryDict('a=1&a=2&c=3')
        # <QueryDict: {'a': ['1', '2'], 'c': ['3']}>
        start = pd.Timestamp(start).to_pydatetime()
        end = pd.Timestamp(end).to_pydatetime()

        # 取出数据
        # 在GoodInSale中没有直接关联Store，所以取Sale
        sales = [
            model_to_dict(sale) for sale in Sale.objects.filter(created__gte=start, created__lte=end)
        ]
        stores = [
            model_to_dict(store) for store in Store.objects.filter(is_own="00")
        ]

        df = pd.DataFrame.from_records(sales)  # Pandas DataFrame
        series = []
        for store in stores:
            temp = df.copy().set_index('created')
            temp = temp[lambda x: x.fk_store == store["id"]]
            temp = temp.resample(period).sum()
            temp = temp.reset_index()
            temp['created'] = temp['created'].apply(
                lambda x: int(
                    x.timestamp()
                ) * 1000  # Javascript Date.getTime() return millisecond rather than second
            )
            temp = temp.loc[:, ['created', target]].to_dict(orient="split")["data"]
            series.append({
                "name": store["title"],
                "data": temp,
            })

        response = {
            "series": series,
            "min": Sale.objects.aggregate(Min("created"))['created__min'],
            "max": Sale.objects.aggregate(Max("created"))['created__max'],
            "stores": stores,
        }

        # response = df.to_dict(orient='split')['data']
        # return HttpResponse('sale data view success!')
        return JsonResponse(response,
                            safe=False)  # In order to serialize objects other than dict you must set the safe parameter to False

    def post(self, request, period, start, end, *args, **kwargs):
        return HttpResponse("test")


class SalePriceIOView(View):
    # 可放缩的图表

    def get(self, request, target, start, end, *args, **kwargs):
        print(target, start, end)

        # QueryDict('a=1&a=2&c=3')
        # <QueryDict: {'a': ['1', '2'], 'c': ['3']}>
        start = pd.Timestamp(start).to_pydatetime()
        end = pd.Timestamp(end).to_pydatetime()

        # 取出数据
        goods = [
            model_to_dict(good) for good in Good.objects.all()
        ]
        df_good = pd.DataFrame.from_records(goods)
        df_good = df_good.loc[:, ["id", "price_sell", "price_purchase"]]
        df_good = df_good.set_index("id")  # inplace default False

        stores = [
            model_to_dict(store) for store in Store.objects.filter(is_own="00")
        ]

        series = []
        for store in stores:
            print(store["title"])
            goodsinsale = [
                model_to_dict(good) for good in
                # GoodInSale.objects.filter(fk_sale__fk_store=store["id"], created__gte=start, created__lte=end)
                GoodInSale.objects.filter(fk_sale__fk_clerk__fk_store=store["id"], created__gte=start, created__lte=end)
            ]
            df_goodinsale = pd.DataFrame.from_records(goodsinsale)
            df_price = df_goodinsale.join(df_good, on="fk_good")
            df_price = df_price.groupby(["price_sell", "price_purchase"]).sum()
            df_price = df_price.reset_index()
            df_price['rate'] = round(df_price['interests'] / df_price['amount'], 2)

            series.append({
                "name": store["title"],
                "data": df_price.loc[:, ["price_sell", "price_purchase", target]].to_dict(orient="split")["data"]
            })

        response = {
            "series": series,
            "min": Sale.objects.aggregate(Min("created"))['created__min'],
            "max": Sale.objects.aggregate(Max("created"))['created__max'],
            "stores": stores,
        }

        # response = df.to_dict(orient='split')['data']
        # return HttpResponse('sale data view success!')
        return JsonResponse(response, safe=False)
        # In order to serialize objects other than dict you must set the safe parameter to False

    def post(self, request, period, start, end, *args, **kwargs):
        return HttpResponse("test")


class SalePeriodView(View):
    # 可放缩的图表

    def get(self, request, store, period, target, start, end, *args, **kwargs):
        print(store, period, target, start, end)

        # QueryDict('a=1&a=2&c=3')
        # <QueryDict: {'a': ['1', '2'], 'c': ['3']}>
        start = pd.Timestamp(start).to_pydatetime()
        end = pd.Timestamp(end).to_pydatetime()

        # 取出数据
        sales = [
            model_to_dict(sale) for sale in Sale.objects.filter(created__gte=start, created__lte=end, fk_store_id=store)
        ]
        df_sale = pd.DataFrame.from_records(sales)

        months = {1: "Jan", 2: "Feb", 3: "Mar", 4: "Apr", 5: "May", 6: "Jun", 7: "Jul", 8: "Aug", "9": "Sep", 10: "Oct",
             11: "Nov", 12: "Dec"}
        weekdays = {0: "周一", 1: "周二", 2: "周三", 3: "周四", 4: "周五", 5: "周六", 6: "周日"}
        # months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
        # weekdays = ["周一", "周二", "周三", "周四", "周五", "周六", "周日"]

        if period == "hour_of_day":
            df_sale[period] = df_sale['created'].apply(lambda cell: cell.hour)  # "{0}时".format(cell.hour)
            df_sale = df_sale.groupby(period).sum().sort_index()
            df_sale = df_sale.reset_index()
            df_sale[period] = df_sale[period].apply(lambda x: "{0}时".format(x))
        elif period == "day_of_week":
            df_sale[period] = df_sale['created'].apply(lambda cell: cell.dayofweek)
            df_sale = df_sale.groupby(period).sum().sort_index()
            df_sale = df_sale.reset_index()
            df_sale[period] = df_sale[period].apply(lambda x: weekdays[x])
        elif period == "day_of_month":
            df_sale[period] = df_sale['created'].apply(lambda cell: cell.day)  # "{0}日".format(cell.day)
            df_sale = df_sale.groupby(period).sum().sort_index()
            df_sale = df_sale.reset_index()
            df_sale[period] = df_sale[period].apply(lambda x: "{0}日".format(x))
        elif period == "month_of_year":
            df_sale[period] = df_sale['created'].apply(lambda cell: cell.month)
            df_sale = df_sale.groupby(period).sum().sort_index()
            df_sale = df_sale.reset_index()
            df_sale[period] = df_sale[period].apply(lambda x: months[x])

        response = {
            "data": df_sale.loc[:, [period,target]].to_dict(orient="split")["data"],
            "min": Sale.objects.aggregate(Min("created"))['created__min'],
            "max": Sale.objects.aggregate(Max("created"))['created__max'],
        }


        # response = df.to_dict(orient='split')['data']
        # return HttpResponse('sale data view success!')
        return JsonResponse(response, safe=False)
        # In order to serialize objects other than dict you must set the safe parameter to False

    def post(self, request, period, start, end, *args, **kwargs):
        return HttpResponse("test")


class SaleLabelView(View):
    # 可放缩的图表

    def get(self, request, store, target, start, end, *args, **kwargs):
        print(store, target, start, end)

        start = pd.Timestamp(start).to_pydatetime()
        end = pd.Timestamp(end).to_pydatetime()

        # 取出数据
        series = [model_to_dict(s) for s in LabelSeries.objects.filter(fk_target__target="good")]
        labels = [model_to_dict(label) for label in Label.objects.filter()]

        df_label_series = pd.DataFrame.from_records(series)
        df_label = pd.DataFrame.from_records(labels)

        # label join label_series => label_series
        df_label_series = df_label_series.set_index("id")
        df_label = df_label.join(df_label_series, on="fk_series", lsuffix='_of_label', rsuffix='_of_label_series')

        # label_good join label_series
        links = [model_to_dict(link) for link in LabelStickGood.objects.filter()]
        df_link = pd.DataFrame.from_records(links)
        df_label = df_label.set_index("id")
        df_link = df_link.join(df_label, on="fk_label", lsuffix='_of_good', rsuffix='_of_label')

        # label_good join good_in_sale
        goodsinsale = [
            model_to_dict(good) for good in GoodInSale.objects.filter(
                created__gte=start,
                created__lte=end,
                # fk_sale__fk_store=store
                fk_sale__fk_clerk__fk_store=store
            )
        ]
        df_goodinsale = pd.DataFrame.from_records(goodsinsale)
        df_link = pd.merge(df_goodinsale, df_link, how='left', on=['fk_good'], suffixes=('_goodinsale', '_link'))

        series_data = []
        drilldown_series = []
        for series_name, series_group in df_link.groupby("title_of_label_series"):
            # s = LabelSeries.objects.get(title=series_name)
            series_data.append({
                    "name": series_name,
                    "y": int(series_group[target].sum()),
                    "drilldown": series_name,
            })
            drilldown_series_data = list()

            for label_name, label_group in series_group.groupby("title_of_label"):
                drilldown_series_data.append(
                    {
                        "name": label_name,
                        "y": int(label_group[target].sum())
                    }
                    # [label_name,int(label_group[target].sum())]
                )

            drilldown_series.append({
                "type": 'pie',
                "id": series_name,
                "data": drilldown_series_data
            })

        response = {
            "series_data": series_data,
            "drilldown_series": drilldown_series,
            "min": Sale.objects.aggregate(Min("created"))['created__min'],
            "max": Sale.objects.aggregate(Max("created"))['created__max'],
        }
        return JsonResponse(response, safe=False)
        # In order to serialize objects other than dict you must set the safe parameter to False



class InventoryView(View):
    # 可放缩的图表

    def get(self, request, store, *args, **kwargs):

        from django.db.models import Sum
        l = []
        for good in Good.objects.all():
            sq = good.good_goodinsale.filter(
                fk_sale__fk_clerk__fk_store=store,
                created__lte=datetime.now()
            ).aggregate(quantity_sum=Sum("quantity"))["quantity_sum"] or 0

            pq = good.good_goodinpurchase.filter(
                fk_purchase__fk_clerk__fk_store=store,
                created__lte=datetime.now()
            ).aggregate(quantity_sum=Sum("quantity"))["quantity_sum"] or 0
            l.append({
                "id": good.id,
                "title": good.title,
                "inventory": pq - sq,
            })

        df_inventory = pd.DataFrame.from_records(l)
        df_inventory = df_inventory[lambda x: x.inventory > 0]
        df_inventory = df_inventory.sort_values(by="inventory", ascending=False)

        d = []
        for good in df_inventory.to_dict(orient="records"):
            d.append({
                "name": good["title"],  # name相同，会堆叠
                "y": good["inventory"],
                "colorByPoint": True,
                "drilldown": good["id"],
            })
        series = [{
            "type": "column",
            "xAxis": 0,
            "colorByPoint": True,
            "labels": {
                "enabled": False
            },
            "name": "Good Inventory",
            "data": d
        }]

        drilldown_series = []
        for each in d:
            goodsinsale = [
                model_to_dict(good) for good in GoodInSale.objects.filter(
                    fk_good=each["drilldown"],
                    fk_sale__fk_clerk__fk_store=store,
                    created__lte=datetime.now()  # min( datetime.now(), datetime(...) )
                )
            ]
            goodsinpurchase = [
                model_to_dict(good) for good in GoodInPurchase.objects.filter(
                    fk_good=each["drilldown"],
                    fk_purchase__fk_clerk__fk_store=store,
                    created__lte=datetime.now()  # min( datetime.now(), datetime(...) )
                )
            ]

            df_good_in_sale = pd.DataFrame.from_records(goodsinsale)
            df_good_in_sale["quantity"] = -df_good_in_sale["quantity"]
            df_good_in_purchase = pd.DataFrame.from_records(goodsinpurchase)

            df_trans = pd.concat([df_good_in_purchase, df_good_in_sale], sort=True)
            df_trans = df_trans.sort_values("created")  # 默认ascending=True
            df_trans["quantity"] = df_trans["quantity"].cumsum()
            df_trans["created"] = df_trans["created"].apply(
                lambda x: int(x.timestamp()) * 1000
            )
            drilldown_series.append({
                "id": each["drilldown"],
                "xAxis":1,
                "type":"line",
                "data": df_trans.loc[:,["created","quantity"]].to_dict(orient="split")["data"],
            })
        drilldown = {
            "series":drilldown_series
        }

        response = {
            "series": series,
            "drilldown": drilldown
        }

        return JsonResponse(response, safe=False)  # In order to serialize objects other than dict you must set the safe parameter to False

    def post(self, request, period, start, end, *args, **kwargs):
        return HttpResponse("test")


class PerformanceView(View):
    # 可放缩的图表

    def get(self, request, store, start, end, *args, **kwargs):
        start = pd.Timestamp(start).to_pydatetime()
        end = pd.Timestamp(end).to_pydatetime()

        clerks = [
            model_to_dict(clerk) for clerk in Clerk.objects.filter(fk_store=store)
        ]
        # id created fk_store

        sales = [
            model_to_dict(sale) for sale in Sale.objects.filter(
                fk_clerk__fk_store=store,
                created__gte=start,
                created__lte=end
            )
        ]
        # id amount interests

        sale_item_list = [
            model_to_dict(sale_item) for sale_item in GoodInSale.objects.filter(
                fk_sale__fk_clerk__fk_store=store,
                created__gte=start,
                created__lte=end
            )
        ]
        # fk_sale fk_quantity

        df_sale_item = pd.DataFrame.from_records(sale_item_list)
        if df_sale_item.empty:
            pd.DataFrame(columns=["id","fk_sale","quantity"])
        df_sale_quantity = df_sale_item.groupby("fk_sale").sum()[["quantity"]]
        df_sale = pd.DataFrame.from_records(sales)
        if df_sale.empty:
            pd.DataFrame(columns=["id", "amount", "interests", "created", "fk_clerk"])
        df_sale = df_sale.join(df_sale_quantity, on="id")[["id", "amount", "interests", "quantity", "created", "fk_clerk"]].dropna()

        df_clerk = pd.DataFrame.from_records(clerks)[["id", "fk_store"]]
        if df_clerk.empty:
            pd.DataFrame(columns=["id","fk_store"])
        df_clerk = df_clerk.set_index("id")
        df_sale = df_sale.join(df_clerk, on="fk_clerk")

        grouped = df_sale.groupby("fk_clerk")

        df_sale_des = grouped.describe().sort_index()

        categories = df_sale_des.index.to_list()

        def id2username(x):  # 计算平方数
            clerk = Clerk.objects.get(id=x)
            return clerk.username
        categories = list(map(id2username,categories))

        df_sale_des = df_sale_des.to_dict(orient="list")

        df_sale_sum = grouped.sum()
        df_sale_sum = df_sale_sum.sort_index().to_dict(orient="list")


        options = {
            "chart": {
                "type":"bar",
                "zoomType": "xy",
                "panning": True,
                "panKey": "shift"
            },

            "title": {
                "text": "Clerk Performance"
            },

            "subtitle": {
                "text": 'Click and drag to zoom in. Hold down shift key to pan.'
            },

            "xAxis": {
                "type": 'category',
                "categories": categories
            },

            "yAxis": [
                {
                    "visible": False,
                    # "type":"linear",
                    # "ceiling": 100,
                    # "floor": 0,
                },
                {
                    "visible": False,
                    # "type": "linear",
                    # "ceiling": 1000,
                    # "floor": 0,
                },

            ],
            "legend": {
                "enabaled": True,
                "align": "center",
                "itemHiddenStyle": {
                    "color": "gray"
                }

            },
            "series": [
                {
                    "name": "成交笔数",
                    "data": df_sale_des[('id', 'count')],
                    "yAxis": 0,
                    "visible": True,
                },
                {
                    "name": "销售额 合计",
                    "data": df_sale_sum["amount"],
                    "yAxis":0,
                    "visible": True,
                },
                {
                    "name": "利润 合计",
                    "data": df_sale_sum["interests"],
                    "yAxis": 0,
                    "visible": True,
                },
                {
                    "name": "数量 合计",
                    "data": df_sale_sum["quantity"],
                    "yAxis": 0,
                    "visible": True,
                },
                {
                    "name": "销售额 单笔平均",
                    "data": df_sale_des[('amount', 'mean')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "利润 单笔平均",
                    "data": df_sale_des[('interests', 'mean')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "数量 单笔平均",
                    "data": df_sale_des[('quantity', 'mean')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "销售额 单笔中位数",
                    "data": df_sale_des[('amount', '50%')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "利润 单笔中位数",
                    "data": df_sale_des[('interests', '50%')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "数量 单笔中位数",
                    "data": df_sale_des[('quantity', '50%')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "销售额 单笔最大",
                    "data": df_sale_des[('amount', 'max')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "利润 单笔最大",
                    "data": df_sale_des[('interests', 'max')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "数量 单笔最大",
                    "data": df_sale_des[('quantity', 'max')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "销售额 单笔最小",
                    "data": df_sale_des[('amount', 'min')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "利润 单笔最小",
                    "data": df_sale_des[('interests', 'min')],
                    "yAxis": 0,
                    "visible": False,
                },
                {
                    "name": "数量 单笔最小",
                    "data": df_sale_des[('quantity', 'min')],
                    "yAxis": 0,
                    "visible": False,
                },

            ]
        }



        response = {
            "options": options,
            "min": Sale.objects.aggregate(Min("created"))['created__min'],
            "max": Sale.objects.aggregate(Max("created"))['created__max'],
        }

        return JsonResponse(response, safe=False)  # In order to serialize objects other than dict you must set the safe parameter to False

    def post(self, request, period, start, end, *args, **kwargs):
        return HttpResponse("test")


class ExpenseAlongTimeView(View):
    # 可放缩的图表

    def get(self, request, store, start, end, *args, **kwargs):
        start = pd.Timestamp(start).to_pydatetime()
        end = pd.Timestamp(end).to_pydatetime()

        mapper = {
            '00': '房租',
            '01': '工资',
            '02': '水电',
            '03': '装修',
            '04': '差旅',
            '05': '其他',
        }
        categories = list(mapper.values())
        expenses = [model_to_dict(expense) for expense in Expense.objects.filter(fk_store=store, created__gte=start, created__lte=end)]
        df = pd.DataFrame.from_records(expenses)
        df["type"] = df["type"].apply(lambda x: mapper[x])

        grouped = df.groupby("type").sum()
        grouped = grouped.reset_index()
        grouped = grouped.sort_values(by=["amount"])
        data = grouped[["type", "amount"]].to_dict(orient="split")["data"]

        response = {
            "categories": categories,
            "data": data,
            "min": Sale.objects.aggregate(Min("created"))['created__min'],
            "max": Sale.objects.aggregate(Max("created"))['created__max'],
        }

        return JsonResponse(response, safe=False)  # In order to serialize objects other than dict you must set the safe parameter to False

    def post(self, request, period, start, end, *args, **kwargs):
        return HttpResponse("test")